Imports System.Data.OleDb
Imports DTO
Public Class LoaiPhong_Dao

    Inherits AbstractDAO

    Public Sub New()

    End Sub

    Public Function LayBang() As DataTable

        Dim dt As New DataTable()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From LoaiPhong"
        ' B4: Thuc thi chuoi strSQL
        Dim da As New OleDbDataAdapter(strSQL, conn)
        da.FillSchema(dt, SchemaType.Source)
        da.Fill(dt)
        ' B5: Dong ket noi CSDL
        conn.Close()
        Return dt

    End Function

    Public Function LayDanhSach() As IList

        Dim ds As New ArrayList()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From LoaiPhong"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            Dim lphgDto As New LoaiPhong_Dto
            lphgDto.MaLoai = dr("MaLoai")
            lphgDto.TenLoaiPHG = dr("TenLoaiPHG")
            lphgDto.DonGia = dr("DonGia")
            ds.Add(lphgDto)
        End While
        ' B5: Dong ket noi CSDL
        dr.Close()
        conn.Close()
        Return ds

    End Function

    Public Sub CapNhatBang(ByVal dt As DataTable)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From LoaiPhong"
        'B4: Thuc thi chuoi strSQL
        Dim da As New OleDbDataAdapter(strSQL, conn)
        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

    Public Function TimKiemLoaiPHG(ByVal mlphg As Integer) As LoaiPhong_Dto

        Dim lphgDto As New LoaiPhong_Dto
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From LoaiPhong Where MaLoai = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@MaLoai", OleDbType.Integer)

        cmd.Parameters("@MaLoai").Value = mlphg

        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            lphgDto = New LoaiPhong_Dto()
            lphgDto.MaLoai = dr("MaLoai")
            lphgDto.TenLoaiPHG = dr("TenLoaiPHG")
            lphgDto.DonGia = dr("DonGia")
        End While
        ' B5: Dong ket noi CSDL
        conn.Close()
        Return lphgDto

    End Function

    Public Sub Them(ByVal lphgDto As LoaiPhong_Dto)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = _
        "Insert into LoaiPhong(TenLoaiPHG, DonGia) values (?, ?)"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@TenLoaiPHG", OleDbType.WChar)
        cmd.Parameters.Add("@DonGia", OleDbType.Double)

        cmd.Parameters("@TenLoaiPHG").Value = lphgDto.TenLoaiPHG
        cmd.Parameters("@DonGia").Value = lphgDto.DonGia

        cmd.ExecuteNonQuery()

        strSQL = "Select @@IDENTITY"
        cmd = New OleDbCommand(strSQL, conn)
        lphgDto.MaLoai = CInt(cmd.ExecuteScalar())
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

    Public Sub Xoa(ByVal mlp As Integer)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Delete From LoaiPhong Where MaLoai = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@MaLoai", OleDbType.Integer)

        cmd.Parameters("@MaLoai").Value = mlp

        cmd.ExecuteNonQuery()
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

    Public Sub Sua(ByVal lphgDto As LoaiPhong_Dto)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = _
        "Update LoaiPhong Set TenLoaiPHG = ? , DonGia = ? Where MaLoai = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@TenLoaiPHG", OleDbType.WChar)
        cmd.Parameters.Add("@DonGia", OleDbType.Double)
        cmd.Parameters.Add("@MaLoai", OleDbType.Integer)

        cmd.Parameters("@TenLoaiPHG").Value = lphgDto.TenLoaiPHG
        cmd.Parameters("@DonGia").Value = lphgDto.DonGia
        cmd.Parameters("@MaLoai").Value = lphgDto.MaLoai

        cmd.ExecuteNonQuery()
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

End Class
